﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Readme_Capture Data Lineage Package Sample</title>
    
    
    
    <style TYPE="text/css">
body
{
background: #FFFFFF;
color: #000000;
font-family:    Verdana;
font-size: medium;
font-style: normal;
font-weight: normal;
margin-top: 0;
margin-bottom:  0;
margin-left:    0;
margin-right:   0;
width:  100%;
}

div.#mainSection
{
font-size: 70%;
width: 100%;
padding-left:    10;
margin-right: 10;
}

div.#mainBody
{
font-size: 90%;
margin-top: 10;
padding-bottom: 20;
}

div.#header
{
background-color: #D2D2D2;
padding-top:    0;
padding-bottom: 0;
padding-left:   10;
padding-right:  0;
width:          100%;
}

div.#header table
{
border-bottom-color: #C8CDDE;
border-bottom-style: solid;
border-bottom-width: 1;
width:  100%;
}

span.#runningHeaderText
{
color: #003399;
font-size: 90%;
}

span.#nsrTitle
{
/*    color: #003399;*/
font-size: 120%;
font-weight: 600;
}

div.#header table td
{
color: #000000;
font-size: 70%;
margin-top: 0;
margin-bottom:  0;
padding-right: 20;
}

div.#header table tr.#headerTableRow3 td
{
padding-bottom: 2;
padding-top: 5;
}

div.#header table.#bottomTable
{
border-top-color: #FFFFFF;
border-top-style: solid;
border-top-width: 1;
text-align: left;
}

div.#footer
{
font-size: 90%;
margin-top: 0;
margin-bottom:  0;
margin-left:    -5;
margin-right:   0;
padding-top:    2;
padding-bottom: 2;
padding-left:   0;
padding-right:  0;
width:  100%;
}

hr.#footerHR
{
border-bottom-color: #EEEEFF;
border-bottom-style: solid;
border-bottom-width: 1;
border-top-color: C8CDDE;
border-top-style: solid;
border-top-width: 1;
height: 3;
color: #D2D2D2;
}

div.section
{
padding-top:    2;
padding-bottom: 2;
padding-right:  15;
width:  100%;
}

.heading
{
color:          #000000;
font-weight:    bold;
margin-top:     18;
margin-bottom:  8;
}

h1.heading
{
color: #000000;
font-size:  150%;
}

.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      150%;
margin-bottom:  4;
}

h2.subHeading
{
color:          #000000;
font-weight:    bold;
font-size:      130%;
}
h3.subHeading
{
color:  #000000;
font-size: 125%;
font-weight: bold;
}

h4.subHeading
{
color: #000000;
font-size: 110%;
font-weight: bold;
}

h4.procedureHeading
{
color: #000080;
font-size: 110%;
font-weight: bold;
}

h5.subHeading
{
color: #000000;
font-size: 100%;
font-weight: bold;
}

img
{
padding-bottom: 10;
}

img.toggle
{
border: 0;
margin-right: 5;
padding-bottom: 10;
}

img.copyCodeImage
{
border: 0;
margin: 1;
margin-right: 3;
padding-bottom: 10;
}

img.downloadCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.viewCodeImage
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.note
{
border: 0;
margin-right: 3;
padding-bottom: 10;
}

img.#membersOptionsFilterImage
{
border: 0;
margin-left: 10;
vertical-align: middle;
padding-bottom: 10;
}

img.#toggleAllImage
{
margin-left: 4;
vertical-align: middle;
padding-bottom: 10;
}

div.#mainSection table
{
border: 0;
font-size: 100%;
width:  100%;
margin-top: 5px;
margin-bottom: 15px;
}

div.#mainSection table tr
{
vertical-align: top;
}

div.#mainSection table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td
{
background: #F2F2F2;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
}

div.#mainSection table td.imageCell
{
white-space: nowrap;
}

div.code
{
width: 98%;
}

div.code table
{
border: 0;
font-size: 95%;
margin-bottom: 5;
width: 100%
}

div.code table th
{
text-align: left;
background: #D8D8D8;
border-bottom-color: #D8D8D8;
border-bottom-style: solid;
border-bottom-width: 1;
color: #000000;
font-weight: bold;
padding-left: 5;
padding-right: 5;
}

div.code table td
{
background: #CCCCCC;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
padding-top: 5;
}

div.alert
{
margin-left: 10;
width: 98%;
}

div.alert table
{
border: 1;
font-size: 100%;
width:  100%;
border: solid 1 #DEDFEF;
}

div.alert table th
{
text-align: left;
background: #D8D8D8;
border-bottom-width: 0;
color: #000000;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

div.alert table td
{
background: #FFFFFF;
border-top-color: #D8D8D8;
border-top-style: solid;
border-top-width: 1;
padding-left: 5;
padding-right: 5;
border: solid 1 #DEDFEF;
}

span.copyCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
float: right;
display: inline;
text-align: right;
}

.downloadCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

.viewCode
{
color: #0000ff;
font-size: 90%;
font-weight: normal;
cursor: hand;
}

div.code pre
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

code
{
font-family:    Monospace, Courier New, Courier;
font-size: 105%;
color:  #000000;
}

dl
{
margin-top: 0;
padding-left:   1;
}

dd
{
margin-bottom:  0;
margin-left:    0;
padding-left:   20;
}

dd p
{
margin-top: 5;
}

ul
{
margin-left: 17;
list-style-type: disc;
}

ul ul
{
margin-bottom: 4;
margin-left: 17;
margin-top: 3;
list-style-type: disc;
}

ol
{
margin-left: 24;
list-style-type: decimal;
}

ol ol
{
margin-left: 24;
margin-top: 3;
list-style-type: lower-alpha;
}

li
{
margin-top: 0;
margin-bottom: 0;
padding-bottom: 0;
padding-top: 0;
margin-left: 5;
}

p
{
margin-bottom: 15;
}

.tip
{
color:  #0000FF;
font-style: italic;
cursor:hand;
text-decoration:underline;
}

.math
{
font-family: Times New Roman;
font-size: 125%
}
.sourceCodeList
{
font-family: Verdana;
font-size: 90%;
}

pre.viewCode
{
width: 100%;
overflow: auto;
}

li:hover table, li.over table
{
background-color: #C0C0C0;
}

li:hover ul, li.over ul
{
background-color: #d2d2d2;
border: 1px solid #000;
display: block;
}
            </style>
  </head>
  <body>
    <!--Topic built:08/05/2008 01:28:33-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="nsrTitle">Readme_Capture Data Lineage Package Sample</span>
          </td>
          <td align="right">
            <span id="headfb" class="feedbackhead">
            </span>
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      <div id="mainBody"><p> 08/05/2008 01:28:33</p>
        
        <font color="DarkGray"> </font><p /> 
        <span id="changeHistory">
        </span>
    <p>
      This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
    </p>
    <p>This sample works with the SQL Server 2005 version of the <b>AdventureWorks</b> OLTP database. To install this database, see <a href="http://go.microsoft.com/fwlink/?LinkId=122077" alt="blocked::http://go.microsoft.com/fwlink/?LinkId=122077"><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Sample Databases for Microsoft SQL Server 2008</linkText></a>. </p>
    <p>The Capture Data Lineage sample is a package that captures audit information. When the package is run, the package loads five identically configured files, and then uses the following Integration Services components to process these files:</p>
    <ul><li>
        An Audit transformation adds columns of historical information, such as the file names, to the data before loading the data into a table.<br />
      </li><li>
        An OLE DB destination loads the data from the files into a table, <b>Lineage_Fact_Table</b>, in the <b>AdventureWorks</b> database.<br />
      </li><li>
        An Execute SQL task both creates the <b>Lineage_Fact_Table</b> table and then truncates the table every time that the package runs.<br />
      </li><li>
        A second Execute SQL task queries the <b>Lineage_Fact_Table</b> and stores the table rows in a variable of the <b>Object</b> data type.<br />
      </li><li>
        A Foreach Loop container extracts the table row values, which are stored in the variable of the <b>Object</b> data type, into separate variables. The container has a Script task that writes the values of the separate variables to a text file.<br />
      </li></ul>
    <p>If you run the sample on a non-English version of Windows, you may have to substitute the localized name of the Program Files folder to open or run the sample.</p>
    <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Important: </th></tr><tr><td>
      Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.<p />
    </td></tr></table><p /></div>
  <h1 class="heading">Requirements</h1><div id="requirementsSection" class="section">
    <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Running this sample package requires the following:</p>
      <ul xmlns=""><li>
          The sample package and data files that it uses must be installed on the local hard disk drive. <br />
        </li><li>
          You must have installed and have administrative permissions on the <b>AdventureWorks</b> OLTP database. <br />
        </li><li>
          If you intend only to run the sample package from the command line, you must install Integration Services. <br />
        </li><li>
          If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio. <br />
        </li></ul>
      <p xmlns="">For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online. </p>
    </content>
  </div><h1 class="heading">Location of the Sample Package</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">If the samples were installed to the default installation location, the Capture Data Lineage sample package, CaptureDataLineage.dtsx, is located in the following folder: </p>
      <p xmlns="">
        C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CaptureDataLineage Sample\Capture Data Lineage\</p>
      <p xmlns="">The following files are required to run this sample package.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              File
            </th>
            <th>
              Description
            </th>
          </tr><tr>
          <td>
            <p>CaptureDataLineage.dtsx</p>
          </td>
          <td>
            <p>The sample package.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data732.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data733.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data734.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data735.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data736.txt</p>
          </td>
          <td>
            <p>Flat file sample data.</p>
          </td>
        </tr><tr>
          <td>
            <p>CheckQueryResults.txt</p>
          </td>
          <td>
            <p>Text file that contains the results of the query that the second Execute SQL task runs.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Running the Sample</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The package can be run from the command line by using the <b>dtexec</b> utility, or can be run in Business Intelligence Development Studio.</p>
      <p xmlns="">If you are using a non-English version of Windows, you may have to update the <b>ConnectionString</b> property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.</p>
      <p xmlns="">For this sample, you may have to update "Program Files" in the <b>ConnectionString</b> property for the Sample Data and Check Query Results connection managers.</p>
      <h4 class="procedureHeading" xmlns="">To run the package by using dtexec</h4><div id="procedureSectionEFBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open a Command Prompt window.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of <b>dtexec</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Type the following command:</p>
              <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CaptureDataLineage Sample\CaptureDataLineage\CaptureDataLineage.dtsx"</pre></td></tr></table></span></div>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Press <b>Enter</b>.</p>
            </content>
          </li></ol></div>
      <p xmlns="">For more information about how to run the package by using the <b>dtexec</b> utility, see the topic, "dtexec Utility", in SQL Server Books Online. </p>
      <h4 class="procedureHeading" xmlns="">To run the package in Business Intelligence Development Studio</h4><div id="procedureSectionEBBHBHA" class="section" xmlns=""><ol><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Open Business Intelligence Development Studio.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">On the <b>File</b> menu, point to <b>Open</b>, and click <b>Project/Solution</b>.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">Locate the CaptureDataLineage Sample folder, and then double-click the file named CaptureDataLineage.sln.</p>
            </content>
          </li><li>
            <content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
              <p xmlns="">In <b>Solution Explorer</b>, right-click CaptureDataLineage.dtsx in the <b>SSIS Packages</b> folder, and then click <b>Execute Package</b>. </p>
            </content>
          </li></ol></div>
    </content></div><h1 class="heading">Components in Sample</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following table lists the Integration Services tasks, containers, data adapters, and transformations that are used within the sample.</p>
      <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;" xmlns=""><tr>
            <th>
              Element
            </th>
            <th>
              Purpose
            </th>
          </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>The Execute SQL task, <b>Create Lineage_Fact_Table</b>, runs an SQL statement that creates the <b>Lineage_Fact_Table</b> table the first time that you run the package, and then truncates the table when you rerun the package.</p>
          </td>
        </tr><tr>
          <td>
            <p>Data Flow task</p>
          </td>
          <td>
            <p>The Data Flow task, <b>Get Data Lineage Information</b>, executes the data flow in the package.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File source</p>
          </td>
          <td>
            <p>The Flat File source, <b>Extract Data from Files</b>, loads the flat file source data and adds a column for the file name to each output row.</p>
          </td>
        </tr><tr>
          <td>
            <p>Audit transformation</p>
          </td>
          <td>
            <p>The Audit transformation, <b>Add Data Lineage Information</b>, adds two new columns for lineage information to each output row. The columns contain user name and start time.</p>
            <div class="alert"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left">Note: </th></tr><tr><td>
              The default length of the column for the user name is 64 characters. If your organization might have user names that exceed 64 characters, you must update the column length by using the <b>Advanced Editor</b> dialog box.<p />
            </td></tr></table><p /></div>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB destination</p>
          </td>
          <td>
            <p>The OLE DB destination, <b>Load Data into Lineage_Fact_Table</b>, loads the results to the <b>Lineage_Fact_Table</b> in the <b>AdventureWorks</b> database.</p>
          </td>
        </tr><tr>
          <td>
            <p>Execute SQL task</p>
          </td>
          <td>
            <p>The Execute SQL task, <b>Query Lineage_Fact_Table</b>, queries the <b>Lineage_Fact_Table</b> table. The task then stores the table rows, as a <b>Full result set</b> result set, in the <b>SQLResults</b> variable of the <b>Object </b>data type.</p>
          </td>
        </tr><tr>
          <td>
            <p>Foreach Loop container</p>
          </td>
          <td>
            <p>The Foreach Loop container, <b>Enumerate Rows in Lineage_Fact_Table</b>, iterates through each table row that is stored in the <b>SQLResults</b> variable. The container then extracts column values into package variables that are mapped to the columns. To enumerate the table rows, the Foreach Loop container uses the Foreach ADO enumerator.</p>
          </td>
        </tr><tr>
          <td>
            <p>Script task</p>
          </td>
          <td>
            <p>The Script task, <b>Write Query Results to Text File</b>, writes the values of the variables that are mapped to the <b>Lineage_Fact_Table</b> columns to a text file.</p>
          </td>
        </tr><tr>
          <td>
            <p>Flat File connection manager</p>
          </td>
          <td>
            <p>The Flat File connection manager, <b>Check Query Results</b>, connects to the file to which the Script task writes the values of variables.</p>
          </td>
        </tr><tr>
          <td>
            <p>Multiple Flat Files connection manager</p>
          </td>
          <td>
            <p>The Multiple Flat Files connection manager, <b>Sample Data</b>, connects to files that have the .txt extension.</p>
          </td>
        </tr><tr>
          <td>
            <p>OLE DB connection manager</p>
          </td>
          <td>
            <p>The OLE DB connection manager, <b>(local).AdventureWorks</b>, connects to the <b>AdventureWorks</b> database on the local server.</p>
          </td>
        </tr></table>
    </content></div><h1 class="heading">Sample Results</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">To see the execution results of the Capture Data Lineage sample package, run the following Transact-SQL query:</p>
      <div class="code" xmlns=""><span codeLanguage="other"><table width="100%" cellspacing="0" cellpadding="0"><tr><th align="left" /></tr><tr><td colspan="2"><pre>Select * from AdventureWorks.dbo.Lineage_Fact_Table
</pre></td></tr></table></span></div>
      <p xmlns="">In these results, you will see the columns populated with the data retrieved from the flat files, with the addition of generated lineage information in the <b>File Name</b>, <b>User Name</b>, and <b>Execution Start Time</b> columns.</p>
    </content></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]--></div>
      <div id="footer">
			
			© 2008 Microsoft Corporation. All rights reserved.
		</div>
    </div>
  </body>
</html>